/**
 * 营销系统数据库操作
 */

const db = require('./db');

// ==================== 优惠券系统 ====================

/**
 * 创建优惠券
 */
const createCoupon = (couponData) => {
  return new Promise((resolve, reject) => {
    const {
      name,
      description,
      type,
      value,
      min_amount = 0,
      max_discount = null,
      start_date,
      end_date,
      total_count = 0,
      per_user_limit = 1,
      created_by
    } = couponData;

    const sql = `
      INSERT INTO coupons 
      (name, description, type, value, min_amount, max_discount, start_date, end_date, total_count, per_user_limit, created_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    db.query(sql, [name, description, type, value, min_amount, max_discount, start_date, end_date, total_count, per_user_limit, created_by], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

/**
 * 获取可用优惠券列表
 */
const getAvailableCoupons = (userId = null) => {
  return new Promise((resolve, reject) => {
    let sql = `
      SELECT c.*, 
             CASE WHEN c.total_count > 0 THEN (c.total_count - c.used_count) ELSE -1 END as remaining_count
      FROM coupons c
      WHERE c.status = 1 
        AND c.start_date <= NOW() 
        AND c.end_date >= NOW()
        AND (c.total_count = 0 OR c.used_count < c.total_count)
    `;

    if (userId) {
      sql += `
        AND c.id NOT IN (
          SELECT coupon_id FROM user_coupons 
          WHERE user_id = ? AND status != 'expired'
          GROUP BY coupon_id 
          HAVING COUNT(*) >= c.per_user_limit
        )
      `;
    }

    sql += ` ORDER BY c.created_at DESC`;

    db.query(sql, userId ? [userId] : [], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

/**
 * 用户领取优惠券
 */
const claimCoupon = (userId, couponId) => {
  return new Promise((resolve, reject) => {
    // 生成优惠券码
    const couponCode = generateCouponCode(couponId);
    
    // 检查优惠券是否可领取
    const checkSql = `
      SELECT c.*, 
             COALESCE(uc.claim_count, 0) as user_claimed
      FROM coupons c
      LEFT JOIN (
        SELECT coupon_id, COUNT(*) as claim_count 
        FROM user_coupons 
        WHERE user_id = ? AND status != 'expired'
        GROUP BY coupon_id
      ) uc ON c.id = uc.coupon_id
      WHERE c.id = ? 
        AND c.status = 1 
        AND c.start_date <= NOW() 
        AND c.end_date >= NOW()
        AND (c.total_count = 0 OR c.used_count < c.total_count)
        AND (uc.claim_count IS NULL OR uc.claim_count < c.per_user_limit)
    `;

    db.query(checkSql, [userId, couponId], (err, coupons) => {
      if (err) {
        reject(err);
        return;
      }

      if (coupons.length === 0) {
        reject(new Error('优惠券不可领取'));
        return;
      }

      const coupon = coupons[0];
      const expiredAt = new Date(coupon.end_date);

      // 插入用户优惠券记录
      const insertSql = `
        INSERT INTO user_coupons (user_id, coupon_id, coupon_code, expired_at)
        VALUES (?, ?, ?, ?)
      `;

      db.query(insertSql, [userId, couponId, couponCode, expiredAt], (err, result) => {
        if (err) {
          reject(err);
        } else {
          // 更新优惠券使用数量
          const updateSql = `UPDATE coupons SET used_count = used_count + 1 WHERE id = ?`;
          db.query(updateSql, [couponId], (updateErr) => {
            if (updateErr) {
              console.error('更新优惠券使用数量失败:', updateErr);
            }
          });
          
          resolve({
            id: result.insertId,
            coupon_code: couponCode,
            ...coupon
          });
        }
      });
    });
  });
};

/**
 * 获取用户优惠券
 */
const getUserCoupons = (userId, status = null) => {
  return new Promise((resolve, reject) => {
    let sql = `
      SELECT uc.*, c.name, c.description, c.type, c.value, c.min_amount, c.max_discount
      FROM user_coupons uc
      JOIN coupons c ON uc.coupon_id = c.id
      WHERE uc.user_id = ?
    `;

    const params = [userId];

    if (status) {
      sql += ` AND uc.status = ?`;
      params.push(status);
    }

    sql += ` ORDER BY uc.received_at DESC`;

    db.query(sql, params, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

/**
 * 使用优惠券
 */
const useCoupon = (userId, couponCode, orderId) => {
  return new Promise((resolve, reject) => {
    const sql = `
      UPDATE user_coupons 
      SET status = 'used', used_at = NOW(), order_id = ?
      WHERE user_id = ? AND coupon_code = ? AND status = 'unused'
    `;

    db.query(sql, [orderId, userId, couponCode], (err, result) => {
      if (err) {
        reject(err);
      } else if (result.affectedRows === 0) {
        reject(new Error('优惠券不存在或已使用'));
      } else {
        resolve(result);
      }
    });
  });
};

// ==================== 积分系统 ====================

/**
 * 获取用户积分信息
 */
const getUserPoints = (userId) => {
  return new Promise((resolve, reject) => {
    const sql = `
      SELECT up.*, ml.name as level_name, ml.discount_rate, ml.point_rate, ml.benefits
      FROM user_points up
      JOIN member_levels ml ON up.level = ml.level
      WHERE up.user_id = ?
    `;

    db.query(sql, [userId], (err, result) => {
      if (err) {
        reject(err);
      } else if (result.length === 0) {
        // 如果用户没有积分记录，创建一个
        createUserPoints(userId).then(() => {
          getUserPoints(userId).then(resolve).catch(reject);
        }).catch(reject);
      } else {
        resolve(result[0]);
      }
    });
  });
};

/**
 * 创建用户积分账户
 */
const createUserPoints = (userId) => {
  return new Promise((resolve, reject) => {
    const sql = `
      INSERT INTO user_points (user_id, total_points, available_points, level)
      VALUES (?, 0, 0, 'bronze')
    `;

    db.query(sql, [userId], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

/**
 * 添加积分
 */
const addPoints = (userId, points, source, sourceId = null, description = '') => {
  return new Promise((resolve, reject) => {
    db.beginTransaction((err) => {
      if (err) {
        reject(err);
        return;
      }

      // 获取当前积分
      const getPointsSql = `SELECT available_points, level_points FROM user_points WHERE user_id = ?`;
      
      db.query(getPointsSql, [userId], (err, result) => {
        if (err) {
          return db.rollback(() => reject(err));
        }

        const currentPoints = result[0]?.available_points || 0;
        const currentLevelPoints = result[0]?.level_points || 0;
        const newBalance = currentPoints + points;
        const newLevelPoints = currentLevelPoints + points;

        // 检查是否需要升级
        const checkLevelSql = `
          SELECT level FROM member_levels 
          WHERE min_points <= ? 
          ORDER BY min_points DESC 
          LIMIT 1
        `;

        db.query(checkLevelSql, [newLevelPoints], (err, levelResult) => {
          if (err) {
            return db.rollback(() => reject(err));
          }

          const newLevel = levelResult[0]?.level || 'bronze';

          // 更新用户积分
          const updatePointsSql = `
            UPDATE user_points 
            SET total_points = total_points + ?, 
                available_points = ?, 
                level_points = ?,
                level = ?
            WHERE user_id = ?
          `;

          db.query(updatePointsSql, [points, newBalance, newLevelPoints, newLevel, userId], (err) => {
            if (err) {
              return db.rollback(() => reject(err));
            }

            // 记录积分变动
            const recordSql = `
              INSERT INTO point_records (user_id, type, points, source, source_id, description, balance_after)
              VALUES (?, 'earn', ?, ?, ?, ?, ?)
            `;

            db.query(recordSql, [userId, points, source, sourceId, description, newBalance], (err, recordResult) => {
              if (err) {
                return db.rollback(() => reject(err));
              }

              db.commit((err) => {
                if (err) {
                  return db.rollback(() => reject(err));
                }

                resolve({
                  points_added: points,
                  new_balance: newBalance,
                  new_level: newLevel,
                  record_id: recordResult.insertId
                });
              });
            });
          });
        });
      });
    });
  });
};

/**
 * 消费积分
 */
const spendPoints = (userId, points, source, sourceId = null, description = '') => {
  return new Promise((resolve, reject) => {
    db.beginTransaction((err) => {
      if (err) {
        reject(err);
        return;
      }

      // 检查积分余额
      const checkSql = `SELECT available_points FROM user_points WHERE user_id = ?`;
      
      db.query(checkSql, [userId], (err, result) => {
        if (err) {
          return db.rollback(() => reject(err));
        }

        const currentPoints = result[0]?.available_points || 0;
        
        if (currentPoints < points) {
          return db.rollback(() => reject(new Error('积分余额不足')));
        }

        const newBalance = currentPoints - points;

        // 更新用户积分
        const updateSql = `
          UPDATE user_points 
          SET available_points = ?, used_points = used_points + ?
          WHERE user_id = ?
        `;

        db.query(updateSql, [newBalance, points, userId], (err) => {
          if (err) {
            return db.rollback(() => reject(err));
          }

          // 记录积分变动
          const recordSql = `
            INSERT INTO point_records (user_id, type, points, source, source_id, description, balance_after)
            VALUES (?, 'spend', ?, ?, ?, ?, ?)
          `;

          db.query(recordSql, [userId, -points, source, sourceId, description, newBalance], (err, recordResult) => {
            if (err) {
              return db.rollback(() => reject(err));
            }

            db.commit((err) => {
              if (err) {
                return db.rollback(() => reject(err));
              }

              resolve({
                points_spent: points,
                new_balance: newBalance,
                record_id: recordResult.insertId
              });
            });
          });
        });
      });
    });
  });
};

/**
 * 获取积分记录
 */
const getPointRecords = (userId, limit = 20, offset = 0) => {
  return new Promise((resolve, reject) => {
    const sql = `
      SELECT * FROM point_records 
      WHERE user_id = ? 
      ORDER BY created_at DESC 
      LIMIT ? OFFSET ?
    `;

    db.query(sql, [userId, limit, offset], (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

// ==================== 工具函数 ====================

/**
 * 生成优惠券码
 */
const generateCouponCode = (couponId) => {
  const timestamp = Date.now().toString(36);
  const random = Math.random().toString(36).substring(2, 8);
  return `CPN${couponId.toString().padStart(4, '0')}${timestamp}${random}`.toUpperCase();
};

/**
 * 计算优惠金额
 */
const calculateDiscount = (coupon, orderAmount) => {
  if (orderAmount < coupon.min_amount) {
    return 0;
  }

  let discount = 0;
  
  switch (coupon.type) {
    case 'cash':
      discount = coupon.value;
      break;
    case 'discount':
      discount = orderAmount * (coupon.value / 100);
      if (coupon.max_discount && discount > coupon.max_discount) {
        discount = coupon.max_discount;
      }
      break;
    default:
      discount = 0;
  }

  return Math.min(discount, orderAmount);
};

module.exports = {
  // 优惠券相关
  createCoupon,
  getAvailableCoupons,
  claimCoupon,
  getUserCoupons,
  useCoupon,
  
  // 积分相关
  getUserPoints,
  createUserPoints,
  addPoints,
  spendPoints,
  getPointRecords,
  
  // 工具函数
  generateCouponCode,
  calculateDiscount
};
